{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "60a3d893",
   "metadata": {},
   "source": [
    "# 多个 dataframe 联合查询\n",
    "\n",
    "> 事先构造好多个 dataframe，列名相同则认为里面的数据类型是相同的，以此来进行联合查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "7149ab0a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<link rel=\"stylesheet\" type=\"text/css\" href=\"https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css\">\n",
       "<script>\n",
       "    require.config({\n",
       "        paths: {\n",
       "            'jquery': 'https://code.jquery.com/jquery-3.5.1',\n",
       "            'datatables.net': 'https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min',\n",
       "            'datatables.net-buttons': 'https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min',\n",
       "            \"echarts\": 'https://cdn.jsdelivr.net/npm/echarts@5.2.2/dist/echarts.min',\n",
       "            \"jszip\": 'https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min',\n",
       "            \"pdfmake\": \"https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min\",\n",
       "            \"vfs_fonts\": \"https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts\",\n",
       "            \"buttons.html5\": \"https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min\",\n",
       "            \"buttons.print\": \"https://cdn.datatables.net/buttons/2.2.3/js/buttons.print.min\",\n",
       "            \"dataTables.searchPanes\": \"https://cdn.datatables.net/searchpanes/2.0.1/js/dataTables.searchPanes.min\",\n",
       "            \"dataTables.select\": \"https://cdn.datatables.net/select/1.4.0/js/dataTables.select.min\",\n",
       "            \"Vue\": \"https://unpkg.com/vue@3/dist/vue.global\",\n",
       "            \"VueRouter\": \"https://unpkg.com/vue-router@4.0.15/dist/vue-router.global\",\n",
       "        },\n",
       "        shims: {\n",
       "            \"html5\": {\n",
       "                deps: [\"datatables.net\", 'datatables.net-buttons'],\n",
       "            },\n",
       "            \"print\": {\n",
       "                deps: [\"datatables.net\", 'datatables.net-buttons'],\n",
       "            },\n",
       "            \"VueRouter\": {\n",
       "                deps: [\"Vue\"],\n",
       "            },\n",
       "            \"jquery\": {\n",
       "                exports: \"$\",\n",
       "            }\n",
       "        }\n",
       "    });\n",
       "</script>\n",
       "\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "%%html\n",
    "\n",
    "<link rel=\"stylesheet\" type=\"text/css\" href=\"https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css\">\n",
    "<script>\n",
    "    require.config({\n",
    "        paths: {\n",
    "            'jquery': 'https://code.jquery.com/jquery-3.5.1',\n",
    "            'datatables.net': 'https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min',\n",
    "            'datatables.net-buttons': 'https://cdn.datatables.net/buttons/2.2.3/js/dataTables.buttons.min',\n",
    "            \"echarts\": 'https://cdn.jsdelivr.net/npm/echarts@5.2.2/dist/echarts.min',\n",
    "            \"jszip\": 'https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min',\n",
    "            \"pdfmake\": \"https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min\",\n",
    "            \"vfs_fonts\": \"https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts\",\n",
    "            \"buttons.html5\": \"https://cdn.datatables.net/buttons/2.2.3/js/buttons.html5.min\",\n",
    "            \"buttons.print\": \"https://cdn.datatables.net/buttons/2.2.3/js/buttons.print.min\",\n",
    "            \"dataTables.searchPanes\": \"https://cdn.datatables.net/searchpanes/2.0.1/js/dataTables.searchPanes.min\",\n",
    "            \"dataTables.select\": \"https://cdn.datatables.net/select/1.4.0/js/dataTables.select.min\",\n",
    "            \"Vue\": \"https://unpkg.com/vue@3/dist/vue.global\",\n",
    "            \"VueRouter\": \"https://unpkg.com/vue-router@4.0.15/dist/vue-router.global\",\n",
    "        },\n",
    "        shims: {\n",
    "            \"html5\": {\n",
    "                deps: [\"datatables.net\", 'datatables.net-buttons'],\n",
    "            },\n",
    "            \"print\": {\n",
    "                deps: [\"datatables.net\", 'datatables.net-buttons'],\n",
    "            },\n",
    "            \"VueRouter\": {\n",
    "                deps: [\"Vue\"],\n",
    "            },\n",
    "            \"jquery\": {\n",
    "                exports: \"$\",\n",
    "            }\n",
    "        }\n",
    "    });\n",
    "</script>\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "19e160e3",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from IPython.display import *"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "5af0a59e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>tom</td>\n",
       "      <td>24</td>\n",
       "      <td>new york</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>jerry</td>\n",
       "      <td>15</td>\n",
       "      <td>tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>ellis</td>\n",
       "      <td>16</td>\n",
       "      <td>shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_1 = pd.DataFrame([[\"tom\",24,\"new york\"],[\"jerry\",15,\"tokyo\"],[\"ellis\",16,\"shanghai\"]], columns=[\"name\", \"age\", \"city\"])\n",
    "HTML(df_1.to_html(escape=False, index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "49c8e22d",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>county</th>\n",
       "      <th>wiki</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>USA</td>\n",
       "      <td><a href='https://baike.baidu.com/item/%E7%BE%8E%E5%9B%BD/125486?fromModule=lemma_search-box&fromtitle=USA&fromid=245069' target='_blank'>百度百科</a></td>\n",
       "      <td>new york</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>JAP</td>\n",
       "      <td><a href='https://baike.baidu.com/item/jap/77600?fromModule=lemma_search-box' target='_blank'>百度百科</a></td>\n",
       "      <td>tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>CHI</td>\n",
       "      <td><a href='https://baike.baidu.com/item/CHI/9746174?fromModule=lemma_search-box' target='_blank'>百度百科</a></td>\n",
       "      <td>shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_2 = pd.DataFrame([[\"USA\",\"<a href='https://baike.baidu.com/item/%E7%BE%8E%E5%9B%BD/125486?fromModule=lemma_search-box&fromtitle=USA&fromid=245069' target='_blank'>百度百科</a>\",\"new york\"],\n",
    "                     [\"JAP\",\"<a href='https://baike.baidu.com/item/jap/77600?fromModule=lemma_search-box' target='_blank'>百度百科</a>\",\"tokyo\"],\n",
    "                     [\"CHI\",\"<a href='https://baike.baidu.com/item/CHI/9746174?fromModule=lemma_search-box' target='_blank'>百度百科</a>\",\"shanghai\"]], columns=[\"county\", \"wiki\", \"city\"])\n",
    "HTML(df_2.to_html(escape=False, index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "5a4cbf3e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>city</th>\n",
       "      <th>landscape</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>new york</td>\n",
       "      <td><img src='https://img0.baidu.com/it/u=1969458144,4026229702&fm=253&fmt=auto&app=138&f=JPEG?w=187&h=187'></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>shanghai</td>\n",
       "      <td><img src='https://img1.baidu.com/it/u=2762309686,4145900845&fm=253&fmt=auto&app=120&f=JPEG?w=144&h=81'></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>tokyo</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_3 = pd.DataFrame([[\"new york\",\"<img src='https://img0.baidu.com/it/u=1969458144,4026229702&fm=253&fmt=auto&app=138&f=JPEG?w=187&h=187'>\"],\n",
    "                     [\"shanghai\", \"<img src='https://img1.baidu.com/it/u=2762309686,4145900845&fm=253&fmt=auto&app=120&f=JPEG?w=144&h=81'>\"],\n",
    "                     [\"tokyo\"]], columns=[\"city\",\"landscape\"])\n",
    "HTML(df_3.to_html(escape=False, index=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6f263471",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from IPython.display import *\n",
    "def union_search(*dfs):\n",
    "    \n",
    "    for df in dfs:\n",
    "        display(HTML(df.reset_index().to_html(escape=False, index=False, classes=\"union_search\")))\n",
    "        display(HTML(\"<hr>\"))\n",
    "        \n",
    "    display(Javascript('''\n",
    "        require(['jquery','datatables.net'], ($,DataTable) => {\n",
    "          $(document).ready(function () {\n",
    "            // Setup - add a text input to each footer cell\n",
    "\n",
    "            $('.union_search').each((k, v) => {\n",
    "              $(v).append('<tfoot></tfoot>')\n",
    "              $('tfoot', v).append($('thead tr', v).clone())\n",
    "            })\n",
    "\n",
    "            $('.union_search tfoot th').each(function () {\n",
    "              var title = $(this).text();\n",
    "              $(this).html(`<input name=\"${title}\" type=\"text\" placeholder=\"Search ${title}\" />`);\n",
    "            });\n",
    "\n",
    "            // DataTable\n",
    "            var table = $('.union_search').DataTable({\n",
    "              initComplete: function () {\n",
    "                // Apply the search\n",
    "                var api = this.api();\n",
    "\n",
    "                api.columns()\n",
    "                  .every(function () {\n",
    "                    var that = this;\n",
    "                    $('input', this.footer()).on('keyup change clear', function () {\n",
    "                      if (that.search() !== this.value) {\n",
    "                        that.search(this.value).draw();\n",
    "                        var name = $('input', that.footer()).attr('name')\n",
    "                        $(`input[name = '${name}']`).val(this.value)\n",
    "                        $(`input[name = '${name}']`).change()\n",
    "                      }\n",
    "                    });\n",
    "                  });\n",
    "                /*\n",
    "                api.$('td').click(function () {\n",
    "                    console.log(this)\n",
    "                    api.search(this.innerHTML).draw();\n",
    "                });\n",
    "                */\n",
    "              },\n",
    "            });\n",
    "          });\n",
    "        })\n",
    "    '''\n",
    "    ))\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "99391fbc",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe union_search\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>index</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>tom</td>\n",
       "      <td>24</td>\n",
       "      <td>new york</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>jerry</td>\n",
       "      <td>15</td>\n",
       "      <td>tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ellis</td>\n",
       "      <td>16</td>\n",
       "      <td>shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<hr>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe union_search\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>index</th>\n",
       "      <th>county</th>\n",
       "      <th>wiki</th>\n",
       "      <th>city</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>USA</td>\n",
       "      <td><a href='https://baike.baidu.com/item/%E7%BE%8E%E5%9B%BD/125486?fromModule=lemma_search-box&fromtitle=USA&fromid=245069' target='_blank'>百度百科</a></td>\n",
       "      <td>new york</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>JAP</td>\n",
       "      <td><a href='https://baike.baidu.com/item/jap/77600?fromModule=lemma_search-box' target='_blank'>百度百科</a></td>\n",
       "      <td>tokyo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>CHI</td>\n",
       "      <td><a href='https://baike.baidu.com/item/CHI/9746174?fromModule=lemma_search-box' target='_blank'>百度百科</a></td>\n",
       "      <td>shanghai</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<hr>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<table border=\"1\" class=\"dataframe union_search\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>index</th>\n",
       "      <th>city</th>\n",
       "      <th>landscape</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>new york</td>\n",
       "      <td><img src='https://img0.baidu.com/it/u=1969458144,4026229702&fm=253&fmt=auto&app=138&f=JPEG?w=187&h=187'></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>shanghai</td>\n",
       "      <td><img src='https://img1.baidu.com/it/u=2762309686,4145900845&fm=253&fmt=auto&app=120&f=JPEG?w=144&h=81'></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>tokyo</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<hr>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "\n",
       "        require(['jquery','datatables.net'], ($,DataTable) => {\n",
       "          $(document).ready(function () {\n",
       "            // Setup - add a text input to each footer cell\n",
       "\n",
       "            $('.union_search').each((k, v) => {\n",
       "              $(v).append('<tfoot></tfoot>')\n",
       "              $('tfoot', v).append($('thead tr', v).clone())\n",
       "            })\n",
       "\n",
       "            $('.union_search tfoot th').each(function () {\n",
       "              var title = $(this).text();\n",
       "              $(this).html(`<input name=\"${title}\" type=\"text\" placeholder=\"Search ${title}\" />`);\n",
       "            });\n",
       "\n",
       "            // DataTable\n",
       "            var table = $('.union_search').DataTable({\n",
       "              initComplete: function () {\n",
       "                // Apply the search\n",
       "                var api = this.api();\n",
       "\n",
       "                api.columns()\n",
       "                  .every(function () {\n",
       "                    var that = this;\n",
       "                    $('input', this.footer()).on('keyup change clear', function () {\n",
       "                      if (that.search() !== this.value) {\n",
       "                        that.search(this.value).draw();\n",
       "                        var name = $('input', that.footer()).attr('name')\n",
       "                        $(`input[name = '${name}']`).val(this.value)\n",
       "                        $(`input[name = '${name}']`).change()\n",
       "                      }\n",
       "                    });\n",
       "                  });\n",
       "                /*\n",
       "                api.$('td').click(function () {\n",
       "                    console.log(this)\n",
       "                    api.search(this.innerHTML).draw();\n",
       "                });\n",
       "                */\n",
       "              },\n",
       "            });\n",
       "          });\n",
       "        })\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.Javascript object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "union_search(df_1, df_2, df_3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8e3ba993",
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
